package lib.persistence.commands;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;

import lib.Field;
public class CreateTable extends CommandAbstract
{
	public boolean CreateMySqlTable(Connection conn , ArrayList<Field> fields, String tableName) throws SQLException
	{
		
		String cmd = String.format("CREATE TABLE `%s` (",tableName);
		int fieldCount = fields.size();
		for (int i =0; i< fieldCount; i++) 
		{
			cmd = cmd.concat(String.format(" `%s` %s %s ", fields.get(i).getName(),fields.get(i).getType() , fields.get(i).getOptions()));

			if (i + 1 < fieldCount)
				cmd = cmd.concat(",");
		}
		cmd = cmd.concat(") ENGINE = MYISAM");
		System.out.println(cmd);
		conn.prepareStatement(String.format("DROP TABLE IF EXISTS `%s`; ",tableName)).execute();
		java.sql.PreparedStatement pstmt = conn.prepareStatement(cmd);
		pstmt.execute();
		
		return true;
	}
	private static ArrayList<Field> getGenericFields() {
		ArrayList<Field> fields = new ArrayList<Field>();
		fields.add(new Field("id" , "INT", "NOT NULL PRIMARY KEY AUTO_INCREMENT"));
		fields.add(new Field("obj","BLOB","NULL"));
		fields.add(new Field("createdAt","TIMESTAMP","ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP"));
		return fields;
	}
	
	/**
	 * Returns a collection of fields in the ProjectPerson mapping table
	 * @return
	 */
	public static ArrayList<Field> getProjectPersonFields()
	{
		ArrayList<Field> fields = new ArrayList<Field>();
		fields.add(new Field("id" , "INT", "NOT NULL PRIMARY KEY AUTO_INCREMENT"));
		fields.add(new Field("project_id","INT" , "NULL"));
		fields.add(new Field("person_id","INT", "NULL"));
		fields.add(new Field("createdAt","TIMESTAMP","ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP"));
		return fields;
	}
	/**
	 * Returns a collection of fields in the Person table
	 * @return
	 */
	public static ArrayList<Field> getPersonFields()
	{
		ArrayList<Field> fields = getGenericFields();
		fields.add(new Field("name","VARCHAR(500)","NOT NULL"));
		fields.add(new Field("title","VARCHAR(500)","NOT NULL"));
		return fields;
	}
	/**
	 * Returns a collection of fields in the Project table
	 * @return
	 */	
	public static ArrayList<Field> getProjectFields()
	{
		ArrayList<Field> fields = getGenericFields();
		fields.add(new Field("name","VARCHAR(500)","NOT NULL"));
		fields.add(new Field("createdBy","INTEGER", "NULL"));
		fields.add(new Field("status","VARCHAR(500)","NULL"));
		return fields;
	}
	/**
	 * Returns a collection of fields in the Event table
	 * @return
	 */
	public static ArrayList<Field> getEventFields()
	{
		ArrayList<Field> fields = getGenericFields();
		
		fields.add(new Field("person_id","INTEGER", "NULL"));
		fields.add(new Field("actor_ids","VARCHAR(500)", "NULL"));
		fields.add(new Field("createdBy","INTEGER", "NULL"));
		fields.add(new Field("project_id","INTEGER", "NULL"));
		fields.add(new Field("status","VARCHAR(500)","NULL"));
		fields.add(new Field("name","VARCHAR(500)","NULL"));
		fields.add(new Field("location","VARCHAR(500)","NULL"));
		fields.add(new Field("eventdate","DATE","NULL"));
		return fields;
	}
	
	/**
	 * Returns a collection of fields in the Milestone table
	 * @return
	 */
	public static ArrayList<Field> getMilestoneFields()
	{
		ArrayList<Field> fields = getGenericFields();
		fields.add(new Field("deliverable_ids","VARCHAR(500)", "NULL"));
		fields.add(new Field("createdBy","INT", "NULL"));
		fields.add(new Field("title","VARCHAR(500)", "NULL"));
		fields.add(new Field("milestoneNumber","INTEGER", "NULL"));
		fields.add(new Field("status","VARCHAR(500)", "NULL"));
		fields.add(new Field("dateachieved","DATE", "NULL"));
		fields.add(new Field("dateprojected","DATE", "NULL"));

		return fields;
	}
	
	/**
	 * Returns a collection of fields in the Deliverable table
	 * @return
	 */
	public static ArrayList<Field> getDeliverableFields()
	{
		ArrayList<Field> fields = getGenericFields();
		fields.add(new Field("createdBy","INTEGER", "NULL"));
		fields.add(new Field("updatedBy","INTEGER", "NULL"));
		fields.add(new Field("updatedAt","TIMESTAMP", "NULL"));
		fields.add(new Field("actor_id","INTEGER", "NULL"));
		fields.add(new Field("project_id","INTEGER", "NULL"));
		fields.add(new Field("title","VARCHAR(500)","NULL"));
		fields.add(new Field("status","VARCHAR(500)","NULL"));
		return fields;
	}
	
	/**
	 * Returns a collection of fields in the Publication table
	 * @return
	 */
	public static ArrayList<Field> getPublicationFields()
	{
		ArrayList<Field> fields = getGenericFields();
		fields.add(new Field("author_ids","VARCHAR(500)", "NULL"));
		fields.add(new Field("location","VARCHAR(500)", "NULL"));
		fields.add(new Field("title","VARCHAR(500)", "NULL"));
		fields.add(new Field("project_id","INTEGER", "NULL"));
		fields.add(new Field("createdBy","INTEGER", "NULL"));
		fields.add(new Field("pubdate","DATE", "NULL"));
		return fields;
	}
}
